package brandroid.BarAndDrink;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteAdapter {

	public static final String MYDATABASE_PATH = "data/data/brandroid.BarAndDrink/databases/";
	public static int MYDATABASE_VERSION = 6;
	public static final String KEY_ID = "_id";
	public static final String BAR_DATABASE_NAME = "baranddrinkLocal";
	public static final String BAR_TABLE = "bar_Names";
	public static final String SPECIAL_TABLE = "bar_Specials";
	public static final String KEY_NAME = "Name";
	public static final String KEY_ADDRESS = "Address";
	public static final String KEY_CITY = "City";
	public static final String KEY_STATE = "State";
	public static final String KEY_ZIP = "Zip";
	public static final String KEY_PHONE = "Phone";
	public static final String KEY_WEB = "WebAddress";
	public static final String KEY_ACTIVE = "Active";
	
	// create table MY_DATABASE (ID integer primary key, Content text not null);
	private static final String Script_Create_BAR_TABLE = "create table bar_Names (_id integer primary key autoincrement, "
			+ "Name text not null, Address text, City text, State text not null, Zip numeric, Phone text, WebAddress text);";

	public static final String SPECIAL_MON = "Monday";
	public static final String SPECIAL_TUE = "Tuesday";
	public static final String SPECIAL_WED = "Wednesday";
	public static final String SPECIAL_THU = "Thursday";
	public static final String SPECIAL_FRI = "Friday";
	public static final String SPECIAL_SAT = "Saturday";
	public static final String SPECIAL_SUN = "Sunday";
	public static final String SPECIAL_ID = "_id";

	private static final String Script_Create_SPECIALS_TABLE = "create table bar_Special (_id integer primary key autoincrement, "
			+ "Sunday text, "
			+ "Monday text, "
			+ "Tuesday text, "
			+ "Wednesday text, "
			+ "Thursday text, "
			+ "Friday text,"
			+ "Saturday);";

	private SQLiteHelper sqLiteHelper;
	private SQLiteDatabase sqLiteDatabase;

	private final Context context;

	private static class SQLiteHelper extends SQLiteOpenHelper {
	
		SQLiteHelper(Context context) {// , String name,
			// CursorFactory factory, int version) {
			super(context, BAR_DATABASE_NAME, null, MYDATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			 db.execSQL(Script_Create_BAR_TABLE); //changed to my database creation
			 db.execSQL(Script_Create_SPECIALS_TABLE);
			 db.execSQL("PRAGMA foreign_keys = ON;");
			 
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			
		}
	}

	public SQLiteAdapter(Context c) {
		this.context = c;
		//sqLiteHelper = new SQLiteHelper(this.context);
	}

	public SQLiteAdapter openToRead() throws android.database.SQLException {
		sqLiteHelper = new SQLiteHelper(context);
		sqLiteDatabase = sqLiteHelper.getReadableDatabase();
		return this;
	}

	public SQLiteAdapter openToWrite() throws android.database.SQLException {
		sqLiteHelper = new SQLiteHelper(context);
		sqLiteDatabase = sqLiteHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		sqLiteDatabase.close();
	}

	public long insert(String content) {

		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_NAME, content);
		return sqLiteDatabase.insert(BAR_TABLE, null, contentValues);
	}

	public int deleteAll() {
		return sqLiteDatabase.delete(BAR_TABLE, null, null);
	}
	
	public Cursor GetBarsByCity(String text){
		String[] columns = new String[] { KEY_ID, KEY_NAME};
		return sqLiteDatabase.query(BAR_TABLE, columns, KEY_CITY
				+ "=?", new String[] { text }, null, null, KEY_NAME, null);

	}
	
	public Cursor GetCities(){
		return sqLiteDatabase.rawQuery("SELECT _id, city FROM bar_Names GROUP BY city", null);
//		String[] columns = new String[] {KEY_ID, KEY_CITY};
//		return sqLiteDatabase.query(BAR_TABLE, columns, null, null, null, null,
//				"City ASC");
	}

	public Cursor queueAll() {
		String[] columns = new String[] { KEY_ID, KEY_NAME, KEY_ADDRESS, KEY_CITY, KEY_STATE, KEY_ZIP,
				KEY_PHONE, KEY_WEB};
		return sqLiteDatabase.query(BAR_TABLE, columns, null, null, null, null,
				"Name ASC");
	}
	
	public Cursor dataBaseNameQuery(String text) {
		String sFilter = KEY_NAME + " LIKE '%" + text + "%'";
		String[] columns = new String[] { KEY_ID, KEY_NAME, KEY_ADDRESS, KEY_CITY, KEY_STATE, KEY_ZIP,
				KEY_PHONE, KEY_WEB};
		Cursor cursor = sqLiteDatabase.query(BAR_TABLE, columns, sFilter, null, null, null, null);
		return cursor;
	}

	public Cursor dataBaseQuery(String text) {
		String[] columns = new String[] { KEY_ID, KEY_NAME, KEY_ADDRESS, KEY_CITY, KEY_STATE, KEY_ZIP,
				KEY_PHONE, KEY_WEB};
		Cursor cursor = sqLiteDatabase.query(BAR_TABLE, columns, KEY_ID
				+ "=?", new String[] { text }, null, null, null, null);
		return cursor;
	}

	public Cursor dataBaseSpecialsQuery(String text) {
		String[] columns = new String[] { SPECIAL_ID, SPECIAL_SUN, SPECIAL_MON,
				SPECIAL_TUE, SPECIAL_WED, SPECIAL_THU, SPECIAL_FRI, SPECIAL_SAT };
		Cursor cursor = sqLiteDatabase.query(SPECIAL_TABLE, columns, SPECIAL_ID
				+ "=?", new String[] { text }, null, null, null, null);

		return cursor;
	}
	
//	public void onCreate(SQLiteDatabase db) {
//		// TODO Auto-generated method stub
//		db.execSQL(Script_Create_BAR_TABLE);
//		db.execSQL(Script_Create_SPECIALS_TABLE);
//		db.execSQL("PRAGMA foreign_keys = ON;");
//	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		try {
			copyDataBase();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void copyDataBase() throws IOException {

		// Open your local db as the input stream
		InputStream myInput = context.getAssets().open("baranddrink");

		// Path to the just created empty db
		String outFileName = MYDATABASE_PATH + BAR_DATABASE_NAME;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	public boolean databaseExists() {
		SQLiteDatabase checkDB = null;
		
		try {
			String myPath = MYDATABASE_PATH + BAR_DATABASE_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);
			if (checkDB != null) {
				try {
					copyDataBase();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				checkDB.close();
				return true;
			} else {
				try {
					//onCreate(sqLiteDatabase);
					copyDataBase();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return false;
			}
		} catch (SQLiteException e) {
			// database does not exist
			//onCreate(sqLiteDatabase);
			try {
				copyDataBase();
			} catch (IOException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		}

	}
}
